﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class PlanWorkDao
    {
        public bool Delete(SqlConnection conn, int planId, string userId, bool isAdministrator)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE ");
            builder.Append("\t[dbo].[Attachment] ");
            builder.Append("SET ");
            builder.Append("\t[DeleteFlag] = 1 ");
            builder.Append("WHERE ");
            builder.Append("\t[AttachmentId] IN ");
            builder.Append("\t\t\t( ");
            builder.Append("\t\t\t\tSELECT ");
            builder.Append("\t\t\t\t\t[AttachmentId] ");
            builder.Append("\t\t\t\tFROM ");
            builder.Append("\t\t\t\t\t[dbo].[Plan_Work] ");
            builder.Append("\t\t\t\tWHERE ");
            builder.Append("\t\t\t\t\t([PlanId] = @PlanId) ");
            builder.Append("\t\t\t\t\tAND ( ");
            builder.Append("\t\t\t\t\t\t([Creator] = @UserId) ");
            builder.Append("\t\t\t\t\t\tOR (@IsAdministrator=1) ");
            builder.Append("\t\t\t\t\t\t) ");
            builder.Append("\t\t\t\t\tAND ISNULL([AttachmentId],'') <> ''\t\t\t\t\t\t ");
            builder.Append("\t\t\t) ");
            builder.Append("DELETE FROM [dbo].[Plan_Work] ");
            builder.Append("WHERE ");
            builder.Append("\t([PlanId] = @PlanId) ");
            builder.Append("\tAND ( ");
            builder.Append("\t\t([Creator] = @UserId) ");
            builder.Append("\t\tOR (@IsAdministrator=1) ");
            builder.Append("\t\t) ");
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@PlanId", SqlDbType.Int);
            commandParameters[0].Value = planId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            commandParameters[2] = new SqlParameter("@IsAdministrator", SqlDbType.Bit);
            commandParameters[2].Value = isAdministrator;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public int Insert(SqlConnection conn, PlanWorkData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT INTO [dbo].[Plan_Work] ");
            builder.Append("( ");
            builder.Append("\t[Name], ");
            builder.Append("\t[Content], ");
            builder.Append("\t[BeginDate], ");
            builder.Append("\t[EndDate], ");
            builder.Append("\t[TypeId], ");
            builder.Append("\t[ToId], ");
            builder.Append("\t[Participator], ");
            builder.Append("\t[Manager], ");
            builder.Append("\t[Creator], ");
            builder.Append("\t[CreateDate], ");
            builder.Append("\t[AttachmentId], ");
            builder.Append("\t[AttachmentComment], ");
            builder.Append("\t[Remark], ");
            builder.Append("\t[CompletedPercent], ");
            builder.Append("\t[Status] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@Name, ");
            builder.Append("\t@Content, ");
            builder.Append("\t@BeginDate, ");
            builder.Append("\t@EndDate, ");
            builder.Append("\t@TypeId, ");
            builder.Append("\t@ToId, ");
            builder.Append("\t@Participator, ");
            builder.Append("\t@Manager, ");
            builder.Append("\t@Creator, ");
            builder.Append("\t@CreateDate, ");
            builder.Append("\t@AttachmentId, ");
            builder.Append("\t@AttachmentComment, ");
            builder.Append("\t@Remark, ");
            builder.Append("\t@CompletedPercent, ");
            builder.Append("\t@Status ");
            builder.Append(") ");
            builder.Append("SELECT SCOPE_IDENTITY() AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[15];
            commandParameters[0] = new SqlParameter("@Name", SqlDbType.NVarChar, 200);
            commandParameters[0].Value = data.Name;
            commandParameters[1] = new SqlParameter("@Content", SqlDbType.NText);
            if (data.ContentIsNull)
            {
                commandParameters[1].Value = DBNull.Value;
            }
            else
            {
                commandParameters[1].Value = data.Content;
            }
            commandParameters[2] = new SqlParameter("@BeginDate", SqlDbType.DateTime);
            if (data.BeginDateIsNull)
            {
                commandParameters[2].Value = DBNull.Value;
            }
            else
            {
                commandParameters[2].Value = data.BeginDate;
            }
            commandParameters[3] = new SqlParameter("@EndDate", SqlDbType.DateTime);
            if (data.EndDateIsNull)
            {
                commandParameters[3].Value = DBNull.Value;
            }
            else
            {
                commandParameters[3].Value = data.EndDate;
            }
            commandParameters[4] = new SqlParameter("@TypeId", SqlDbType.Int);
            commandParameters[4].Value = data.TypeId;
            commandParameters[5] = new SqlParameter("@ToId", SqlDbType.NText);
            if (data.ToIdIsNull)
            {
                commandParameters[5].Value = DBNull.Value;
            }
            else
            {
                commandParameters[5].Value = data.ToId;
            }
            commandParameters[6] = new SqlParameter("@Participator", SqlDbType.NText);
            if (data.ParticipatorIsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.Participator;
            }
            commandParameters[7] = new SqlParameter("@Manager", SqlDbType.NText);
            if (data.ManagerIsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.Manager;
            }
            commandParameters[8] = new SqlParameter("@Creator", SqlDbType.NVarChar, 20);
            commandParameters[8].Value = data.Creator;
            commandParameters[9] = new SqlParameter("@CreateDate", SqlDbType.DateTime);
            commandParameters[9].Value = data.CreateDate;
            commandParameters[10] = new SqlParameter("@AttachmentId", SqlDbType.NVarChar, 50);
            if (data.AttachmentIdIsNull)
            {
                commandParameters[10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[10].Value = data.AttachmentId;
            }
            commandParameters[11] = new SqlParameter("@AttachmentComment", SqlDbType.NText);
            if (data.AttachmentCommentIsNull)
            {
                commandParameters[11].Value = DBNull.Value;
            }
            else
            {
                commandParameters[11].Value = data.AttachmentComment;
            }
            commandParameters[12] = new SqlParameter("@Remark", SqlDbType.NText);
            if (data.RemarkIsNull)
            {
                commandParameters[12].Value = DBNull.Value;
            }
            else
            {
                commandParameters[12].Value = data.Remark;
            }
            commandParameters[13] = new SqlParameter("@CompletedPercent", SqlDbType.Int);
            commandParameters[13].Value = data.CompletedPercent;
            commandParameters[14] = new SqlParameter("@Status", SqlDbType.Int);
            commandParameters[14].Value = data.Status;
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public void Manage(SqlConnection conn, int planId, string userId, bool isAdministrator, int operation)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF EXISTS(SELECT [PlanId] FROM [dbo].[Plan_Work] WHERE (([Creator] = @UserId) OR (@IsAdministrator = 1)) AND ([PlanId] = @PlanId)) ");
            builder.Append("BEGIN ");
            builder.Append("\tIF @Operation=0 ");
            builder.Append("\t\tUPDATE [dbo].[Plan_Work] ");
            builder.Append("\t\tSET ");
            builder.Append("\t\t\t[BeginDate] = CAST(CAST(year(GETDATE()) AS varchar)  + '-' + CAST(month(GETDATE()) AS varchar) + '-' + CAST(day(GETDATE()) AS varchar) AS datetime), ");
            builder.Append("\t\t\t[EndDate] = NULL ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[PlanId] = @PlanId ");
            builder.Append("\tIF @Operation=1 ");
            builder.Append("\t\tUPDATE [dbo].[Plan_Work] ");
            builder.Append("\t\tSET ");
            builder.Append("\t\t\t[EndDate] = CAST(CAST(year(DATEADD(day,-1,GETDATE())) AS varchar)  + '-' + CAST(month(DATEADD(day,-1,GETDATE())) AS varchar) + '-' + CAST(day(DATEADD(day,-1,GETDATE())) AS varchar) AS datetime) ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[PlanId] = @PlanId ");
            builder.Append("\tIF @Operation=2 ");
            builder.Append("\t\tUPDATE [dbo].[Plan_Work] ");
            builder.Append("\t\tSET ");
            builder.Append("\t\t\t[EndDate] = NULL ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[PlanId] = @PlanId ");
            builder.Append("END ");
            SqlParameter[] commandParameters = new SqlParameter[4];
            commandParameters[0] = new SqlParameter("@PlanId", SqlDbType.Int);
            commandParameters[0].Value = planId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            commandParameters[2] = new SqlParameter("@IsAdministrator", SqlDbType.Bit);
            commandParameters[2].Value = isAdministrator;
            commandParameters[3] = new SqlParameter("@Operation", SqlDbType.Int);
            commandParameters[3].Value = operation;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
        }

        public DataTable SelectByPlanId(SqlConnection conn, int planId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT ");
            builder.Append("\t[Plan_Work].[PlanId], ");
            builder.Append("\t[Plan_Work].[Name], ");
            builder.Append("\t[Plan_Work].[Content], ");
            builder.Append("\t[Plan_Work].[BeginDate], ");
            builder.Append("\t[Plan_Work].[EndDate], ");
            builder.Append("\t[Plan_Work].[TypeId], ");
            builder.Append("\t[Plan_Type].[TypeName], ");
            builder.Append("\t[Plan_Work].[ToId], ");
            builder.Append("\tdbo.GetDeptNames(CAST([Plan_Work].[ToId] AS nvarchar(2000))) AS ToName, ");
            builder.Append("\t[Plan_Work].[Participator], ");
            builder.Append("\tdbo.GetUserNames([Plan_Work].[Participator]) AS ParticipatorName, ");
            builder.Append("\t[Plan_Work].[Manager], ");
            builder.Append("\tdbo.GetUserNames([Plan_Work].[Manager]) AS ManagerName, ");
            builder.Append("\t[Plan_Work].[Creator], ");
            builder.Append("\t[Users].[UserName] AS CreatorName, ");
            builder.Append("\t[Department].[DeptName], ");
            builder.Append("\t[Plan_Work].[CreateDate], ");
            builder.Append("\t[Plan_Work].[AttachmentId], ");
            builder.Append("\t[Plan_Work].[AttachmentComment], ");
            builder.Append("\t[Plan_Work].[Remark], ");
            builder.Append("\t[Plan_Work].[CompletedPercent], ");
            builder.Append("\t[Plan_Work].[Status]\t ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Plan_Work], ");
            builder.Append("\t[dbo].[Plan_Type], ");
            builder.Append("\t[dbo].[Users], ");
            builder.Append("\t[dbo].[Department] ");
            builder.Append("WHERE ");
            builder.Append("\t[Plan_Work].[PlanId] = @PlanId ");
            builder.Append("\tAND [Plan_Work].[TypeId] = [Plan_Type].[TypeId] ");
            builder.Append("\tAND [Plan_Work].[Creator] = [Users].[UserId] ");
            builder.Append("\tAND [Users].[DeptId] = [Department].[DeptId] ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@PlanId", SqlDbType.Int) };
            commandParameters[0].Value = planId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Plan_Work";
            return set.Tables[0];
        }

        public DataTable SelectPaged(SqlConnection conn, int pagesize, int pageindex, bool docount, string userId, bool isAdministrator)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF(@docount=1) ");
            builder.Append("\tSELECT COUNT([PlanId]) FROM [dbo].[Plan_Work] WHERE ([Creator] = @UserId) OR (@IsAdministrator = 1) ");
            builder.Append("ELSE ");
            builder.Append("\tBEGIN ");
            builder.Append("\t\tDECLARE @IndexTable TABLE(ID int identity(1,1),PK int) ");
            builder.Append("\t\tDECLARE @PageLowerBound int ");
            builder.Append("\t\tDECLARE @PageUpperBound int ");
            builder.Append("\t\tSET @PageLowerBound=(@pageindex-1)*@pagesize ");
            builder.Append("\t\tSET @PageUpperBound=@PageLowerBound+@pagesize ");
            builder.Append("\t\tSET rowcount @PageUpperBound ");
            builder.Append("\t\t ");
            builder.Append("\t\tINSERT INTO @IndexTable(PK) SELECT [PlanId] FROM [dbo].[Plan_Work] WHERE ([Creator] = @UserId) OR (@IsAdministrator = 1)  ORDER BY [PlanId] DESC ");
            builder.Append("\t\t ");
            builder.Append("\t\tSELECT ");
            builder.Append("\t\t\t[Plan_Work].[PlanId], ");
            builder.Append("\t\t\t[Plan_Work].[Name], ");
            builder.Append("\t\t\t[Plan_Work].[BeginDate], ");
            builder.Append("\t\t\t[Plan_Work].[EndDate], ");
            builder.Append("\t\t\t[Plan_Type].[TypeName],\t\t ");
            builder.Append("\t\t\tdbo.GetDeptNames(CAST([Plan_Work].[ToId] AS nvarchar(2000))) AS ToName, ");
            builder.Append("\t\t\t[Plan_Work].[CompletedPercent], ");
            builder.Append("\t\t\t[Plan_Work].[Status], ");
            builder.Append("\t\t\t[Users].[UserName],\t\t\t ");
            builder.Append("\t\t\t@pageindex AS pageindex ");
            builder.Append("\t\tFROM ");
            builder.Append("\t\t\t[dbo].[Plan_Work],  ");
            builder.Append("\t\t\t[dbo].[Plan_Type], ");
            builder.Append("\t\t\t[dbo].[Users],  ");
            builder.Append("\t\t\t@IndexTable t\t ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[Plan_Work].[PlanId] = t.PK ");
            builder.Append("\t\t\tAND [Plan_Work].[TypeId] = [Plan_Type].[TypeId] ");
            builder.Append("\t\t\tAND [Plan_Work].[Creator] = [Users].[UserId] ");
            builder.Append("\t\t\tAND t.ID>@PageLowerBound  ");
            builder.Append("\t\t\tAND t.ID<=@PageUpperBound  ");
            builder.Append("\t\t ORDER BY ");
            builder.Append("\t\t\t[Plan_Work].[PlanId] DESC ");
            builder.Append("\tEND ");
            SqlParameter[] commandParameters = new SqlParameter[5];
            commandParameters[0] = new SqlParameter("@pagesize", SqlDbType.Int);
            commandParameters[0].Value = pagesize;
            commandParameters[1] = new SqlParameter("@pageindex", SqlDbType.Int);
            commandParameters[1].Value = pageindex;
            commandParameters[2] = new SqlParameter("@docount", SqlDbType.Bit);
            commandParameters[2].Value = docount;
            commandParameters[3] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[3].Value = userId;
            commandParameters[4] = new SqlParameter("@IsAdministrator", SqlDbType.Bit);
            commandParameters[4].Value = isAdministrator;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Plan_Work";
            return set.Tables[0];
        }

        public DataTable Today(SqlConnection conn, int deptId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT [PlanId],[Name] FROM [dbo].[Plan_Work] ");
            builder.Append("WHERE((ISNULL([BeginDate],'1900-1-1') <= '" + DateTime.Now.ToString("d") + "') AND (ISNULL([EndDate],'2100-1-1') >= '" + DateTime.Now.ToString("d") + "')) ");
            builder.Append("AND ((CAST([ToId] AS varchar) = 'All_DEPT') OR (CHARINDEX('," + deptId.ToString() + ",',',' + CAST([ToId] AS varchar) + ',') > 0)) ");
            builder.Append("ORDER BY [PlanId] DESC");
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString());
            set.Tables[0].TableName = "Plan_Work";
            return set.Tables[0];
        }

        public bool Update(SqlConnection conn, PlanWorkData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Plan_Work] ");
            builder.Append("SET ");
            builder.Append("\t[Name] = @Name, ");
            builder.Append("\t[Content] = @Content, ");
            builder.Append("\t[BeginDate] = @BeginDate, ");
            builder.Append("\t[EndDate] = @EndDate, ");
            builder.Append("\t[TypeId] = @TypeId, ");
            builder.Append("\t[ToId] = @ToId, ");
            builder.Append("\t[Participator] = @Participator, ");
            builder.Append("\t[Manager] = @Manager, ");
            builder.Append("\t[Creator] = @Creator, ");
            builder.Append("\t[CreateDate] = @CreateDate, ");
            builder.Append("\t[AttachmentId] = @AttachmentId, ");
            builder.Append("\t[AttachmentComment] = @AttachmentComment, ");
            builder.Append("\t[Remark] = @Remark, ");
            builder.Append("\t[CompletedPercent] = @CompletedPercent, ");
            builder.Append("\t[Status] = @Status ");
            builder.Append("WHERE ");
            builder.Append("\t[PlanId] = @PlanId ");
            SqlParameter[] commandParameters = new SqlParameter[0x10];
            commandParameters[0] = new SqlParameter("@PlanId", SqlDbType.Int);
            commandParameters[0].Value = data.PlanId;
            commandParameters[1] = new SqlParameter("@Name", SqlDbType.NVarChar, 200);
            commandParameters[1].Value = data.Name;
            commandParameters[2] = new SqlParameter("@Content", SqlDbType.NText);
            if (data.ContentIsNull)
            {
                commandParameters[2].Value = DBNull.Value;
            }
            else
            {
                commandParameters[2].Value = data.Content;
            }
            commandParameters[3] = new SqlParameter("@BeginDate", SqlDbType.DateTime);
            if (data.BeginDateIsNull)
            {
                commandParameters[3].Value = DBNull.Value;
            }
            else
            {
                commandParameters[3].Value = data.BeginDate;
            }
            commandParameters[4] = new SqlParameter("@EndDate", SqlDbType.DateTime);
            if (data.EndDateIsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.EndDate;
            }
            commandParameters[5] = new SqlParameter("@TypeId", SqlDbType.Int);
            commandParameters[5].Value = data.TypeId;
            commandParameters[6] = new SqlParameter("@ToId", SqlDbType.NText);
            if (data.ToIdIsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.ToId;
            }
            commandParameters[7] = new SqlParameter("@Participator", SqlDbType.NText);
            if (data.ParticipatorIsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.Participator;
            }
            commandParameters[8] = new SqlParameter("@Manager", SqlDbType.NText);
            if (data.ManagerIsNull)
            {
                commandParameters[8].Value = DBNull.Value;
            }
            else
            {
                commandParameters[8].Value = data.Manager;
            }
            commandParameters[9] = new SqlParameter("@Creator", SqlDbType.NVarChar, 20);
            commandParameters[9].Value = data.Creator;
            commandParameters[10] = new SqlParameter("@CreateDate", SqlDbType.DateTime);
            commandParameters[10].Value = data.CreateDate;
            commandParameters[11] = new SqlParameter("@AttachmentId", SqlDbType.NVarChar, 50);
            if (data.AttachmentIdIsNull)
            {
                commandParameters[11].Value = DBNull.Value;
            }
            else
            {
                commandParameters[11].Value = data.AttachmentId;
            }
            commandParameters[12] = new SqlParameter("@AttachmentComment", SqlDbType.NText);
            if (data.AttachmentCommentIsNull)
            {
                commandParameters[12].Value = DBNull.Value;
            }
            else
            {
                commandParameters[12].Value = data.AttachmentComment;
            }
            commandParameters[13] = new SqlParameter("@Remark", SqlDbType.NText);
            if (data.RemarkIsNull)
            {
                commandParameters[13].Value = DBNull.Value;
            }
            else
            {
                commandParameters[13].Value = data.Remark;
            }
            commandParameters[14] = new SqlParameter("@CompletedPercent", SqlDbType.Int);
            commandParameters[14].Value = data.CompletedPercent;
            commandParameters[15] = new SqlParameter("@Status", SqlDbType.Int);
            commandParameters[15].Value = data.Status;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }
    }
}

